package cn.edu.hunau.utils;


import cn.edu.hunau.exception.DaoException;

import java.io.InputStream;
import java.sql.*;
import java.util.Objects;
import java.util.Properties;

/**
 * 连接数据库的工具类
 *
 * @author hb
 * <p>
 * 2019-08-27 09:22:37
 */
public class DBUtils {
    private static final String USERNAME;
    private static final String PWD;
    private static final String URL;
    private static final String DRIVERCLASS;

    static {
        Properties pro = new Properties();
        //读取配置文件获得连接信息
        InputStream is = DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");

        try {
            pro.load(is);
            USERNAME = pro.getProperty("jdbc.username");
            PWD = pro.getProperty("jdbc.pwd");
            URL = pro.getProperty("jdbc.url");
            DRIVERCLASS = pro.getProperty("jdbc.driverClass");
            //加载驱动
            Class.forName(DRIVERCLASS);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 对查询的语句进行封装
     *
     * @param <T>
     * @param sql
     * @param rowMapper
     * @param args
     * @return
     */
    public static <T> T query(String sql, RowMapper<T> rowMapper, Object... args) {
        // 1. 获得Connection对象
        Connection conn = null;
        // 2.声明PreparedStatement对象
        PreparedStatement pstm = null;
        // 结果集对象
        ResultSet rs = null;
        try {
            conn = DBUtils.newConnection();
            pstm = conn.prepareStatement(sql);
            // 绑定参数
            if (args != null && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstm.setObject(i + 1, args[i]);
                }
            }
            rs = pstm.executeQuery();
            return rowMapper.mapper(rs);
        } catch (Exception e) {
            throw new DaoException(e);
        } finally {
            DBUtils.close(conn, pstm, rs);
        }
    }

    /**
     * 对返回自动主键值的封装
     *
     * @param sql
     * @param columnNames
     * @param args
     * @return
     * @throws DaoException
     */
    public static int updateReturnAutoincrementPk(String sql, String[] columnNames, Object... args)
            throws DaoException {
        // 1. 获得Connection对象
        Connection conn = null;
        // 2.声明PreparedStatement对象
        PreparedStatement pstm = null;
        // 结果集对象
        ResultSet rs = null;
        int id = -1;
        try {
            conn = DBUtils.newConnection();
            //columnNames指定主键字段的名称
            pstm = conn.prepareStatement(sql, columnNames);
            // 绑定参数
            if (args != null && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstm.setObject(i + 1, args[i]);
                }
            }
            pstm.executeUpdate();
            // 获得封装主键值的结果集
            rs = pstm.getGeneratedKeys();
            if (rs.next()) {
                //只能使用序号，无法使用字段名获取
                id = rs.getInt(1);
            }
            return id;
        } catch (Exception e) {
            throw new DaoException(e);
        } finally {
            DBUtils.close(conn, pstm, rs);
        }
    }

    /**
     * 封装了DML(update,insert,delete)语句的操作
     *
     * @throws DaoException
     */
    public static void update(String sql, Object... args) throws DaoException {
        // 1. 获得Connection对象
        Connection conn = null;
        // 2.声明PreparedStatement对象
        PreparedStatement pstm = null;
        try {
            conn = DBUtils.newConnection();
            pstm = conn.prepareStatement(sql);
            // 绑定参数
            if (args != null && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstm.setObject(i + 1, args[i]);
                }
            }
            // 执行SQL语句
            pstm.executeUpdate();
        } catch (Exception e) {
            throw new DaoException(e);
        } finally {
            DBUtils.close(conn, pstm);
        }
    }

    /**
     * 手动管理事务的update方法
     *
     * @param sql
     * @param args
     * @throws DaoException
     */
    public static void updateTransaction(String sql, Object... args) throws DaoException {
        // 1. 获得Connection对象
        Connection conn = null;
        // 2.声明PreparedStatement对象
        PreparedStatement pstm = null;
        try {
            // 获得Connection对象，同时手动管理事务
            conn = DBUtils.beginTransaction();
            pstm = conn.prepareStatement(sql);
            // 绑定参数
            if (args != null && args.length > 0) {
                for (int i = 0; i < args.length; i++) {
                    pstm.setObject(i + 1, args[i]);
                }
            }
            // 执行SQL语句
            pstm.executeUpdate();
        } catch (Exception e) {
            throw new DaoException(e);
        } finally {
            // 不能在Dao中手动释放Connection对象
            DBUtils.close(null, pstm);
        }
    }

    public static void commit(Connection conn) throws DaoException {
        try {
            if (conn != null)
                conn.commit();
        } catch (SQLException e) {
            throw new DaoException(e);
        }
    }

    public static void rollback(Connection conn) throws DaoException {
        try {
            if (conn != null)
                conn.rollback();
        } catch (SQLException e) {
            throw new DaoException(e);
        }
    }

    private static final ThreadLocal<Connection> TL = new ThreadLocal<>();  //线程的局部变量,原理是在map中用当前线程作为key

    /**
     * 手动管理事务的Connection对象
     *
     * @return
     * @throws DaoException
     */
    public static Connection beginTransaction() throws DaoException {
        // 1.尝试从ThreadLocal获得Connection对象
        Connection conn = TL.get();
        try {
            //判断conn是否已经存在<是否已近关闭
            if (conn == null || conn.isClosed()) {
                // 获得一个新的Connection对象
                conn = newConnection();
                // 设置手动管理事务 (connection默认是手动提交事务)
                conn.setAutoCommit(false);
                // 将connection对象存储到ThreadLocal中
                TL.set(conn);   // map.put(当前线程,conn)
            }
            return conn;
        } catch (Exception e) {
            throw new DaoException(e);
        }
    }

    /**
     * 获得一个新的Connection对象
     *
     * @return
     * @throws DaoException
     */
    public static Connection newConnection() throws DaoException {
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(URL, USERNAME, PWD);
        } catch (SQLException e) {
            throw new DaoException(e);
        }

        return conn;
    }

    /**
     * 手动关闭Connection对象
     *
     * @param conn
     * @throws DaoException
     */
    public static void close(Connection conn) throws DaoException {
        close(conn, null, null);
    }

    /**
     * 手动关闭Connection对象
     *
     * @param conn
     * @throws DaoException
     */
    public static void close(Connection conn, Statement pstm) throws DaoException {

        close(conn, pstm, null);
    }

    /**
     * 手动关闭Connection对象
     *
     * @param conn
     * @throws DaoException
     */
    public static void close(Connection conn, Statement pstm, ResultSet rs) throws DaoException {
        try {
            if (!Objects.isNull(rs)) {
                rs.close();
            }
            if (pstm != null) {
                pstm.close();
            }
            if (conn != null) {
                conn.close();
            }

        } catch (SQLException e) {
            throw new DaoException(e);
        }
    }
}
